﻿--获取员工工资
CREATE PROCEDURE [dbo].[proc_Employee_Wage_GetList]
(
		@CompanyId int,
		@DepId int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex int       
)
as

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DepId


	
	;WITH list As(Select ROW_NUMBER() OVER (ORDER BY Id DESC)AS Row,
		Id,
		ENumber,
		ETempNumber,
		EName,
		Sex,
		(select CompanyName from Company where id=companyid )as CompanyName,
		(select DName from Department where id=departmentid) as DName,
		ZhiWu,
		IsNull((select PC.title from post_category PC where PC.id=employee.PostCategory),'') as PostCategoryName,
		IsNull((select P.PostName from Post P where P.id=employee.PostId),'') as PostName,
		IsNull((select G.Title from Grade G where G.id=employee.GradeId),'') as GradeName,
		IsNull((select G.Wage from Grade G where G.id=employee.GradeId),0) as BasicWage,
		IsNull((select P.PostWage from Post P where P.id=employee.PostId),0) as PostWage,
		IsNull((datediff(dd,ruzhidate,getdate())/360*100),0) as WorkWage,
		IsNull((select  P.JxWage from Post P where  P.id=employee.PostId),0) as JxWage,
		IsNull((select P.ManQin from Post P where P.id=employee.PostId),0) as ManQin,
		IsNull((select Sum(EA.Amount) from Employee_Allowance EA where EA.PostId=employee.PostId and EA.CategoryId=1),0) as Allowance,
		IsNull((select Sum(EA.Amount) from Employee_Allowance EA where EA.PostId=employee.PostId and EA.CategoryId=2),0) as TiCheng,
		IsNull((select Sum(EA.Amount) from Employee_Allowance EA where EA.PostId=employee.PostId and EA.CategoryId=3),0) as MTiCheng,
		IsNull((select Sum(EA.Amount) from Employee_Allowance EA where EA.PostId=employee.PostId and EA.CategoryId=4),0) as YjBonus
		
		from employee 
		
		Where (@CompanyId=0 Or CompanyId=@CompanyId)
		and (@DepId=0 Or DepartmentId in(Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T))
		and (@Stext='' Or Ename like '%'+@Stext+'%')
		and State <> 5
	)
	
	Select  * From list Where Row Between @StartIndex and @EndIndex
set nocount off
RETURN
